package cn.plugins.generator.utils

import cn.plugins.generator.entity.ColumnEntity
import cn.plugins.generator.entity.DatabaseInfoEntity
import cn.plugins.generator.entity.TableEntity
import java.sql.*

/**
 * 创建于 2020-06-02 16:03
 *
 * @author jiangyun
 * @类说明：h2数据库连接工具类
 */
object H2DatabaseUtil {
    private var connection: Connection? = null

    /**
     * 获取数据库连接
     */
    @Throws(Exception::class)
    fun getConnection(databaseInfo: DatabaseInfoEntity?): Connection? {
        return try {
            if (this.connection != null) {
                return this.connection
            }
            Class.forName("org.h2.Driver")
            DriverManager.getConnection(
                databaseInfo?.url,
                databaseInfo?.userName,
                databaseInfo?.password
            )
        } catch (e: SQLException) {
            e.printStackTrace()
            println("数据库连接失败！")
            throw e
        } catch (e: ClassNotFoundException) {
            e.printStackTrace()
            println("获取数据库驱动失败！")
            throw e
        }
    }

    /**
     * 获取表信息
     *
     * @param url           连接地址
     * @param databaseName  数据库名
     * @param userName      用户名
     * @param password      密码
     * @param tableName     表名称
     */
    @Throws(Exception::class)
    fun getTableInfo(
        url: String,
        databaseName: String,
        userName: String,
        password: String,
        tableName: String
    ): TableEntity? {
        val connection: Connection? = getConnection(DatabaseInfoEntity(url, userName, password))
        if (connection != null) {
            var ps: PreparedStatement? = null
            var rs: ResultSet? = null
            return try {
                val sql = """
                    SELECT 
                    TABLE_NAME,
                    remarks AS table_comment 
                    FROM INFORMATION_SCHEMA.TABLES 
                    WHERE TABLE_SCHEMA = 'PUBLIC' AND TABLE_CATALOG = '${databaseName}' AND table_name =${tableName};
                """.trimIndent()
                ps = connection.prepareStatement("")
                rs = ps.executeQuery()

                var tableInfo = TableEntity()
                if (rs.next()) {
                    val tempTableName: String = if (tableName.isNotBlank() && tableName.substring(
                            tableName.length - 2,
                            tableName.length - 1
                        ) == "表"
                    ) {
                        tableName.substring(0, tableName.length - 2)
                    } else {
                        tableName
                    }
                    tableInfo.tableName = tempTableName
                    tableInfo.comment = rs.getString("comment")
                }

                tableInfo
            } catch (e: SQLException) {
                throw e
            } finally {
                try {
                    ps!!.close()
                    rs!!.close()
                } catch (e: SQLException) {
                    e.printStackTrace()
                }
            }
        }

        return null
    }

    /**
     * 获取表的列值信息
     *
     * @param url           连接地址
     * @param databaseName  数据库名称
     * @param userName      用户名
     * @param password      密码
     * @param tableName     表名称
     */
    @Throws(Exception::class)
    fun findTableColumns(
        url: String,
        databaseName: String,
        userName: String,
        password: String,
        tableName: String
    ): MutableList<ColumnEntity>? {
        val conn = getConnection(DatabaseInfoEntity(url, userName, password))
        var ps: PreparedStatement? = null
        var rs: ResultSet? = null
        return try {
            val sql = """
                SELECT
                    c.COLLATION_NAME AS columnName,
                    c.TYPE_NAME AS dataType,
                    c.COLUMN_DEFAULT AS columnDefault,
                    c.IS_NULLABLE AS isNullable,
                    c.CHARACTER_MAXIMUM_LENGTH AS characterMaximumLength,
                    c.REMARKS AS columnComment,
                    CASE WHEN tc.CONSTRAINT_TYPE = 'PRIMARY KEY' THEN 'PRI'
                    ELSE ''
                END AS columnKey
                FROM
                INFORMATION_SCHEMA.COLUMNS c
                LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu ON
                (kcu.TABLE_SCHEMA = c.TABLE_SCHEMA
                AND kcu.TABLE_NAME = c.TABLE_NAME
                AND kcu.COLUMN_NAME = c.COLUMN_NAME)
                LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc ON
                (tc.CONSTRAINT_SCHEMA = kcu.CONSTRAINT_SCHEMA
                AND tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME)
                WHERE
                c.TABLE_CATALOG = '${databaseName}'
                AND c.TABLE_SCHEMA = 'PUBLIC'
                AND c.TABLE_NAME = '${tableName}'
                ORDER BY
                c.ORDINAL_POSITION
            """.trimIndent()
            ps = conn!!.prepareStatement(sql)
            rs = ps.executeQuery()
            val columns: MutableList<ColumnEntity> = ArrayList()
            var column: ColumnEntity
            while (rs.next()) {
                column = ColumnEntity()
                column.columnName = rs.getString("columnName")
                column.dataType = rs.getString("dataType")
                column.columnDefault = rs.getString("columnDefault")
                column.isNullable = rs.getString("isNullable")
                column.characterMaximumLength = rs.getString("characterMaximumLength")
                column.columnComment = rs.getString("columnComment")
                column.columnKey = rs.getString("columnKey")

                columns.add(column)
            }
            columns
        } catch (e: SQLException) {
            throw e
        } finally {
            try {
                ps!!.close()
                rs!!.close()
            } catch (e: SQLException) {
                e.printStackTrace()
            }
        }
    }
}